package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import www.lagou.entity.Phone;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class PhoneQueryTest {

    //2) 需求1:  查询价格高于2000元，生产日期是2019年之前的所有手机
    @Test
    public void testFind01() throws SQLException {

        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL
        String sql = "select pname from phone where price > 2000 and prodate < '2019-01-01'";
        //select pname
        //from phone
        //where price > 2000
        //and prodate < '2019-01-01';

        //3.执行查询
        //Object[] query = qr.query(sql, new ArrayHandler()); //注意，这样只能查询到一条
        List<Object[]> query = qr.query(sql, new ArrayListHandler());

        //4.获取数据
        System.out.println("查询到的价格高于2000元，生产日期是2019年之前的所有手机有：");
        //4.遍历集合获取数据
        for (Object[] objects : query) {
            System.out.println(Arrays.toString(objects));
        }
    }

    //3) 需求2:  查询所有颜色是白色的手机信息
    @Test
    public void testFind02() throws SQLException {

        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL
        String sql = "select * from phone where color = ?";
        //select *
        //from phone
        //where color = '白色';

        //3.执行查询
        List<Phone> phoneList = qr.query(sql, new BeanListHandler<Phone>(Phone.class), "白色");

        //4.获取数据
        System.out.println("查询到的白色手机有：" + phoneList.size() + "个");
        for (Phone phone : phoneList) {
            System.out.println(phone);
        }
    }
}
